﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Resources;

using FU.SE0208.G1.Controller;

namespace FU.SE0208.G1.CustomControl
{
    public partial class AdvanceSearch : UserControl
    {
        // Error
        private const string ERR_LOAD_OBJECTS_FAIL = "Fail to load Objects! There are some problems with database server";
        private const string ERR_LOAD_TABLE_COLUMN_FAIL = "Fail to load Tables' columns! There are some problems with database server";
        private const string ERR_LOAD_TABLE_DATA_FAIL = "Fail to load Tables' data! There are some problems with database server";
        private const string ERR_SEARCH_FAIL_FIELD = "Search is not done! Please choose value for FIELD in row ";
        private const string ERR_SEARCH_FAIL_OPERATOR = "Search is not done! Please complete search condition(s) in row ";
        private const string ERR_LIMIT_CONDITION = "There are only 20 conditions can be used";
        private const string ERR_SEARCH_FAIL_NO_CONDITION = "There is not any condition to search";
        private const string ERR = "";

        // Message
        private const string SMS_SEARCH_NUMBER_RESULT = "Result(s)";
        private const string SMS = "";


        // Const
        private const string STR_TABLE_NAME = "TableName";

        // In database we build procedures to get search tables. Search_ABC
        private const string STR_SEARCH_TABLE_PREFIX = "Search_";

        // Store procedure to get template data
        private const string STR_TEMPLATE_DATA = "STO_TEMPLATE_DATA";

        // Column name
        private const string STR_COLUMN_FIELD = "Field";
        private const string STR_COLUMN_OPERATOR = "Operator";
        private const string STR_COLUMN_VALUE = "Value";
        private const string STR_COLUMN_TYPE = "Type";
        private const string STR_COLUMN_SHOW = "Show";
        private const string STR_COLUMN_DEL = "Del";

        // Const data
        private string[] ARR_TYPE = { "OR", "AND" };
        private string[][] ARR_OPERATOR = null;
        private string[] ARR_OBJECT_DISPLAY = { "Product", "Serial", "Category", "Manufacturer", "StorageIn", "Order", "Customer", "Employee", "WorkSession"};
        private string[] ARR_OBJECT_VALUE   = { "Product", "Serial", "Category", "Manufacturer", "StorageIn", "Order", "Customer", "Employee", "WorkSession" };

        private const int INT_MAX_CONDITION = 20;

        // Table search conditions
        private DataSet dsData = null;

        // Count the number of conditions
        private int iCountCondition = 0;

        // Load control
        private bool bLoaded = false;

        /*
         * -------------------------------------------------------------------------------
         */

        private void AdvanceSearch_Load(object sender, EventArgs e)
        {
            // Init dataset to store data
            dsData = new DataSet();

            ARR_OPERATOR = new string[2][];
            ARR_OPERATOR[0] = new string[] { ">", ">=", "<", "<=", "=", "<>" };
            ARR_OPERATOR[1] = new string[] { "LIKE", "=", "IN" };

            // Load combobox
            if (loadObject() == false)
            {
                MessageBox.Show(ERR_LOAD_OBJECTS_FAIL);
                return;
            }

            // Load all the tables' data
            if (loadTemplateData() == false)
            {
                MessageBox.Show(ERR_LOAD_TABLE_DATA_FAIL);
                return;
            }

            // Load all the tables' column in DB
            if (loadTableColumn() == false)
            {
                MessageBox.Show(ERR_LOAD_TABLE_COLUMN_FAIL);
                return;
            }

            // Load done
            bLoaded = true;

            // Init search conditions table
            initDgvSearchConditions();

            // Add first search condition
            addSearchCondition();
        }

        private static int getOperator(string type)
        {
            if (type.IndexOf("string") >= 0 || type.IndexOf("String") >= 0) return 1;
            else return 0;
        }

        public AdvanceSearch()
        {
            InitializeComponent();
        }

        // Load all the table_name in DB into the combobox
        private bool loadObject()
        {
            //DataSet ds = new DataSet();

            //string sSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";

            //if (DbHandler.getQuery(dsData, sSQL, STR_TABLE_NAME) == false)
            //{
            //    return false;
            //}

            dsData.Tables.Add(STR_TABLE_NAME);
            DataTable dt = dsData.Tables[STR_TABLE_NAME];

            dt.Columns.Add("DISPLAY_NAME", Type.GetType("System.String"));
            dt.Columns.Add("TABLE_NAME", Type.GetType("System.String"));

            for (int i = 0; i < ARR_OBJECT_DISPLAY.Length; i++)
            {
                DataRow dr = dt.NewRow();

                dr["DISPLAY_NAME"] = ARR_OBJECT_DISPLAY[i];
                dr["TABLE_NAME"] = ARR_OBJECT_VALUE[i];

                dt.Rows.Add(dr);
            }

            cbbObject.DataSource = dsData.Tables[STR_TABLE_NAME];
            cbbObject.DisplayMember = "DISPLAY_NAME";
            cbbObject.ValueMember = "TABLE_NAME";

            return true;
        }

        // Load all the tables' columns
        private bool loadTableColumn()
        {
            //for (int i = 0; i < dsData.Tables[STR_TABLE_NAME].Rows.Count; i++)
            //{
            //    string name = dsData.Tables[STR_TABLE_NAME].Rows[i]["TABLE_NAME"].ToString();

            //    string sSQL = "SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + name + "'";
            //    string sTable = name;

            //    if (DbHandler.getQuery(dsData, sSQL, sTable) == false)
            //    {
            //        return false;
            //    }
            //}

            for (int i = 0; i < dsData.Tables[STR_TABLE_NAME].Rows.Count; i++)
            {
                string name = dsData.Tables[STR_TABLE_NAME].Rows[i]["TABLE_NAME"].ToString();

                string sTable = STR_SEARCH_TABLE_PREFIX + name;

                dsData.Tables.Add(name);
                DataTable dt = dsData.Tables[name];

                dt.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
                dt.Columns.Add("DATA_TYPE", Type.GetType("System.String"));
                dt.Columns.Add("ROW", Type.GetType("System.Int32"));

                for (int j = 0; j < dsData.Tables[sTable].Columns.Count; j++)
                {
                    DataRow dr = dt.NewRow();

                    dr["COLUMN_NAME"] = dsData.Tables[sTable].Columns[j].ColumnName;
                    dr["DATA_TYPE"] = dsData.Tables[sTable].Columns[j].DataType;
                    dr["ROW"] = j;

                    dt.Rows.Add(dr);
                }
            }

            return true;
        }

        // Load all the tables' data
        private bool loadTemplateData()
        {
            for (int i = 0; i < dsData.Tables[STR_TABLE_NAME].Rows.Count; i++)
            {
                string name = dsData.Tables[STR_TABLE_NAME].Rows[i]["TABLE_NAME"].ToString();

                string sSQL = STR_SEARCH_TABLE_PREFIX + name;
                string sTable = sSQL;

                if (DbHandler.getQuery(dsData, sSQL, sTable) == false)
                {
                    return false;
                }
            }

            //if (DbHandler.getQuery(dsData, STR_TEMPLATE_DATA, null) == false)
            //{
            //    return false;
            //}

            return true;
        }

        // Init DataGridView search conditions
        private void initDgvSearchConditions()
        {
            if (!bLoaded) return;

            iCountCondition = 0;

            // Column Field
            DataGridViewComboBoxColumn cField = new DataGridViewComboBoxColumn();
            cField.HeaderText = STR_COLUMN_FIELD;
            cField.Name = STR_COLUMN_FIELD;
            cField.DataPropertyName = STR_COLUMN_FIELD;
            dgvConditions.Columns.Add(cField);

            // Column Operator
            DataGridViewComboBoxColumn cOperator = new DataGridViewComboBoxColumn();
            cOperator.HeaderText = STR_COLUMN_OPERATOR;
            cOperator.Name = STR_COLUMN_OPERATOR;
            cOperator.DataPropertyName = STR_COLUMN_OPERATOR;
            dgvConditions.Columns.Add(cOperator);

            // Column Value
            DataGridViewTextBoxColumn cValue = new DataGridViewTextBoxColumn();
            cValue.HeaderText = STR_COLUMN_VALUE;
            cValue.Name = STR_COLUMN_VALUE;
            cValue.DataPropertyName = STR_COLUMN_VALUE;
            dgvConditions.Columns.Add(cValue);

            // Column Type
            DataGridViewComboBoxColumn cType = new DataGridViewComboBoxColumn();
            cType.HeaderText = STR_COLUMN_TYPE;
            cType.Name = STR_COLUMN_TYPE;
            cType.DataPropertyName = STR_COLUMN_TYPE;
            cType.Width = 50;
            dgvConditions.Columns.Add(cType);

            // Column Show
            DataGridViewCheckBoxColumn cShow = new DataGridViewCheckBoxColumn();
            cShow.HeaderText = STR_COLUMN_SHOW;
            cShow.Name = STR_COLUMN_SHOW;
            cShow.DataPropertyName = STR_COLUMN_SHOW;
            cShow.Width = 50;
            dgvConditions.Columns.Add(cShow);

            // Column Del
            DataGridViewImageColumn cDel = new DataGridViewImageColumn();
            cDel.HeaderText = STR_COLUMN_DEL;
            cDel.Width = 30;
            cDel.Name = STR_COLUMN_DEL;
            cDel.DataPropertyName = STR_COLUMN_DEL;
            dgvConditions.Columns.Add(cDel);
        }

        private void addSearchCondition()
        {
            if (!bLoaded) return;

            if (iCountCondition == INT_MAX_CONDITION)
            {
                MessageBox.Show(ERR_LIMIT_CONDITION);
            }
            else
            {
                try
                {
                    dgvConditions.Rows.Add(1);

                    // Column Field
                    DataGridViewComboBoxCell tmp = (DataGridViewComboBoxCell)dgvConditions[STR_COLUMN_FIELD, iCountCondition];
                    DataTable dt = new DataTable();
                    dt = dsData.Tables[cbbObject.SelectedValue.ToString()].Copy();
                    tmp.DataSource = dt;
                    //tmp.DataSource = dsData.Tables[cbbObject.SelectedValue.ToString()];
                    tmp.ValueMember = "ROW";
                    tmp.DisplayMember = "COLUMN_NAME";

                    //tmp.Tag = iCountCondition;

                    // Column Operator

                    // Column Type
                    ((DataGridViewComboBoxCell)dgvConditions[STR_COLUMN_TYPE, iCountCondition]).DataSource = ARR_TYPE;

                    // Column Show
                    dgvConditions[STR_COLUMN_SHOW, iCountCondition].Value = true;

                    // Column Del
                    dgvConditions[STR_COLUMN_DEL, iCountCondition].Value = global::CustomControl.Properties.Resources.cancel;

                    iCountCondition++;
                }
                catch (Exception ex)
                {
                    // Ignore
                }
            }
        }

        // Set Object
        public bool setObject(string value)
        {
            if (cbbObject.Items.Contains(value) == false) return false;
            cbbObject.SelectedText = value;

            return true;
        }

        private void btnAddCondition_Click(object sender, EventArgs e)
        {
            addSearchCondition();
        }

        private string buildSqlCommand()
        {
            string tmp = "";

            return tmp;
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (iCountCondition == 0)
            {
                MessageBox.Show(ERR_SEARCH_FAIL_NO_CONDITION);
                return;
            }

            // Generate condition string
            string sCondition = "", tmp1 = "", tmp2 = "";

            for (int i = 0; i < dgvConditions.Rows.Count; i++)
            {
                try
                {
                    string t = dgvConditions[STR_COLUMN_FIELD, i].Value.ToString();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ERR_SEARCH_FAIL_FIELD + (i + 1).ToString());
                    return;
                }

                if (dgvConditions[STR_COLUMN_OPERATOR, i].Value == null && dgvConditions[STR_COLUMN_VALUE, i].Value == null && dgvConditions[STR_COLUMN_TYPE, i].Value == null) continue;

                try
                {
                    string sFieldValue = dsData.Tables[cbbObject.SelectedValue.ToString()].Rows[(int)dgvConditions[STR_COLUMN_FIELD, i].Value]["COLUMN_NAME"].ToString();
                    string sFieldType = dsData.Tables[cbbObject.SelectedValue.ToString()].Rows[(int)dgvConditions[STR_COLUMN_FIELD, i].Value]["DATA_TYPE"].ToString();

                    string sOperator = dgvConditions[STR_COLUMN_OPERATOR, i].Value.ToString();
                    string sValue = dgvConditions[STR_COLUMN_VALUE, i].Value.ToString();
                    string type = dgvConditions[STR_COLUMN_TYPE, i].Value.ToString();

                    if (getOperator(sFieldType) == 1) sValue = "'" + sValue + "'";
                    if (type.Equals("OR"))
                    {
                        if (tmp1.Equals(""))
                        {
                            tmp1 = sFieldValue + " " + sOperator + " " + sValue;
                        }
                        else
                        {
                            tmp1 += (" OR " + sFieldValue + " " + sOperator + " " + sValue);
                        }
                    }
                    else
                    {
                        if (tmp2.Equals(""))
                        {
                            tmp2 = sFieldValue + " " + sOperator + " " + sValue;
                        }
                        else
                        {
                            tmp2 += (" AND " + sFieldValue + " " + sOperator + " " + sValue);
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ERR_SEARCH_FAIL_OPERATOR + (i + 1).ToString());
                    return;
                }
            }

            if (tmp1.Equals(""))
            {
                if (!tmp2.Equals(""))
                {
                    sCondition = tmp2;
                }
            }
            else
            {
                if (tmp2.Equals(""))
                {
                    sCondition = tmp1;
                }
                else
                {
                    sCondition = tmp1 + " OR (" + tmp2 + ")";
                }
            }

            DataView dv = new DataView(dsData.Tables[STR_SEARCH_TABLE_PREFIX + cbbObject.SelectedValue.ToString()]);
            dv.RowFilter = sCondition;

            // Show the result
            dgvResult.DataSource = dv;

            // Hiden all column
            for (int i = 0; i < dgvResult.Columns.Count; i++)
            {
                dgvResult.Columns[i].Visible = false;
            }

            //try
            //{
            // Show the columns which added
            for (int i = 0; i < dgvConditions.Rows.Count; i++)
            {
                if ((bool)dgvConditions[STR_COLUMN_SHOW, i].Value == true)
                {
                    string name = dsData.Tables[cbbObject.SelectedValue.ToString()].Rows[(int)dgvConditions[STR_COLUMN_FIELD, i].Value]["COLUMN_NAME"].ToString();
                    dgvResult.Columns[name].Visible = true;
                }
            }

            // Display the number of result(s)
            lblResult.Text = dv.Count.ToString() + " " + SMS_SEARCH_NUMBER_RESULT;

            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ERR_SEARCH_FAIL);
            //}
        }

        private void cbbObject_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Clear DataGridView
            dgvConditions.Rows.Clear();
            dgvConditions.Columns.Clear();

            // Init again
            initDgvSearchConditions();

            // Add first search condition
            addSearchCondition();
        }

        private void dgvConditions_CellValidated(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {
                try
                {
                    string type = dsData.Tables[cbbObject.SelectedValue.ToString()].Rows[(int)dgvConditions[STR_COLUMN_FIELD, e.RowIndex].Value]["DATA_TYPE"].ToString();

                    //MessageBox.Show(type);

                    // Column Operator
                    DataGridViewComboBoxCell tmp = (DataGridViewComboBoxCell)dgvConditions[STR_COLUMN_OPERATOR, e.RowIndex];
                    tmp.DataSource = ARR_OPERATOR[getOperator(type)].Clone();
                }
                catch (Exception ex)
                {
                    // Ignore
                    //MessageBox.Show(ex.Message);
                }
            }
        }

        private void dgvConditions_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 5)
            {
                try
                {
                    dgvConditions.Rows.RemoveAt(e.RowIndex);
                    iCountCondition--;
                }
                catch (Exception ex)
                {
                    // Ignore
                    //MessageBox.Show(ex.Message);
                }
            }
        }

        private void dgvConditions_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            // do nothing
        }

        private void lblObject_Click(object sender, EventArgs e)
        {

        }

        private void grbSearchConditions_Enter(object sender, EventArgs e)
        {

        }

        private void dgvConditions_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void grbResult_Enter(object sender, EventArgs e)
        {

        }

        private void dgvResult_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void lblResult_Click(object sender, EventArgs e)
        {

        }
    }
}
